<?php
class routes_model extends CI_Model{
	private $routes_origen = array();
	private $routes_destino = array();
	private $resultados = array();
	private $resultados_guia = array();
	private $no_estar = array();
	private $contador = array();
	
	function __construct(){
		parent::__construct();
		$this->load->database();
	}
	
	/**
	 * Obtiene la informacion de una ruta, coordenadas, estaciones, nombre, etc.
	 * @param unknown_type $params
	 */
	public function getRoute($params){
		$res_stat = $this->db->query("SELECT 
				r.id, 
				r.name, 
				10 AS distance, 
				c.city, 
				rg.region 
			FROM routes AS r 
				INNER JOIN regions AS rg ON rg.id = r.regions_id 
				INNER JOIN routes_cities AS rc ON rc.routes_id = r.id 
				INNER JOIN cities AS c ON c.id = rc.cities_id 
			WHERE r.id = ".$params['route']." ORDER BY r.name ASC");
		if($res_stat->num_rows() > 0){
			$data_rout = array('route' => $res_stat->row_array());
			$data_rout['route']['coordinates'] = $data_rout['route']['marker'] = array();
			
			//Obtenemos las coordenadas de la ruta
			$res_coor = $this->db->query("
			SELECT 
				lat, lon, 
				IFNULL(direction, '') AS direction,
				groups 
			FROM routes_coordenates 
			WHERE routes_id = ".$params['route']." ORDER BY sequence ASC");
			
			foreach($res_coor->result_array() as $row){
				$data_rout['route']['coordinates'][] = $row;
			}
			
			//Obtenemos otros marcadores de la ruta si tiene
			$res_coor = $this->db->query("
			SELECT 
				name,
				lat, lon
			FROM routes_coors_marker 
			WHERE routes_id = ".$params['route']." ORDER BY sequence ASC");
			
			foreach($res_coor->result_array() as $row){
				$data_rout['route']['marker'][] = $row;
			}
			
			return $data_rout;
		}
		return false;
	}
	
	/**
	 * Obtiene las rutas de una ciudad espesifica, 
	 * regresa paginado los resultados
	 * @param unknown_type $params.
	 */
	public function getRoutesCity($params){
		$query = Sys::pagination("
		SELECT 
			r.id, r.name, 
			'' AS distance 
		FROM routes AS r 
			INNER JOIN routes_cities AS rc ON r.id = rc.routes_id 
		WHERE rc.cities_id = ".$params['city']." 
			AND r.name <> ''
		GROUP BY r.id
		ORDER BY r.name ASC", $params, true);
			
		$res_stat = $this->db->query($query['query']);
		if($res_stat->num_rows() > 0){
			$data_rout = array('routes' => array(), 'total_rows' => $query['total_rows']);
			
			foreach($res_stat->result_array() as $row){
				$data_rout['routes'][] = $row;
			}
			
			return $data_rout;
		}
		return false;
	}
	
	/**
	 * Obtiene la(s) ruta(s) que llevan del origen al destino usando dos puntos, 
	 * o de un punto obtiene las rutas cercanas
	 * @param unknown_type $params
	 */
	public function getRoutes2Coor($params){
		$point = new gPoint();
		$point->setLongLat($params['filter_lon1'], $params['filter_lat1']);
		$point->convertLLtoTM();
		$punto1 = $point->getUTM();
		$search_2points = false;
		
		//obtenemos las rutas del origen
		$query_origen = Sys::pagination("
		SELECT r.id, r.name, distance2points(rco.north, rco.west, ".$punto1['northing'].", ".$punto1['easting'].") AS distance
		FROM routes AS r 
			INNER JOIN routes_cities AS rc ON r.id = rc.routes_id 
			INNER JOIN (
				SELECT 
					id, 
					city, 
					latitude, 
					longitude, 
					distance2points(north, west, ".$punto1['northing'].", ".$punto1['easting'].") AS distance
				FROM cities_with_routes
				ORDER BY distance ASC LIMIT 1
			) AS ci ON ci.id = rc.cities_id
			INNER JOIN routes_coordenates AS rco ON r.id = rco.routes_id
		WHERE inside_circle(rco.north, rco.west, ".$punto1['northing'].", ".$punto1['easting'].", ".$params['filter_radio'].") = 1
		GROUP BY r.id
		ORDER BY distance ASC", $params, true);
		
		
		//si estan las coordenadas 2 obtenemos las rutas destino
		if(isset($params['filter_lat2']) && isset($params['filter_lon2'])){
			if(is_numeric($params['filter_lat2']) && is_numeric($params['filter_lon2'])){
				$point->setLongLat($params['filter_lon2'], $params['filter_lat2']);
				$point->convertLLtoTM();
				$punto2 = $point->getUTM();
				$search_2points = true;
				
				$res_stat_dest = $this->db->query("SELECT r.id, r.name, distance2points(rco.north, rco.west, 
					".$punto2['northing'].", ".$punto2['easting'].") AS distance
				FROM routes AS r 
					INNER JOIN routes_cities AS rc ON r.id = rc.routes_id 
					INNER JOIN (
						SELECT 
							id, 
							city, 
							latitude, 
							longitude, 
							distance2points(north, west, ".$punto2['northing'].", ".$punto2['easting'].") AS distance
						FROM cities_with_routes
						ORDER BY distance ASC LIMIT 1
					) AS ci ON ci.id = rc.cities_id
					INNER JOIN routes_coordenates AS rco ON r.id = rco.routes_id
				WHERE inside_circle(rco.north, rco.west, ".$punto2['northing'].", ".$punto2['easting'].", ".$params['filter_radio'].") = 1
				GROUP BY r.id
				ORDER BY distance ASC");
				
				if($query_origen['resultset']->num_rows() > 0 && $res_stat_dest->num_rows() > 0){
					$data_rout = array('routes' => array(), 'total_rows' => 0);
					
					//rutas origen
					foreach($query_origen['resultset']->result_array() as $row){
						$this->routes_origen[] = $row['id'];
					}
					//rutas destino
					foreach($res_stat_dest->result_array() as $row){
						$this->routes_destino[] = $row['id'];
					}
					
					//ejecutamos el algoritmo de busqueda
					$this->buscar($this->routes_origen, null, '', null, $params);
					
					$resul = $res_sub = array();
					foreach($query_origen['resultset']->result_array() as $row){
						$res_sub = Sys::buscarArray2($this->resultados_guia, 0, $row['id']);
						foreach($res_sub as $otr){
							$row['id'] = implode(',', $otr);
							$row['transfer'] = lang('txt_transbordar', count($otr)-1);
							$data_rout['routes'][] = $row;
						}
					}
					
					return $data_rout;
				}
			}
		}else{
			$res_stat = $this->db->query($query_origen['query']);
			if($res_stat->num_rows() > 0){
				$data_rout = array('routes' => array(), 'total_rows' => $query['total_rows']);
				
				foreach($res_stat->result_array() as $row){
					$data_rout['routes'][] = $row;
				}
				
				return $data_rout;
			}
		}
		return false;
	}
	
	private function buscar($vec_b, $ind_no=null, $guia='', $ind_f=null, &$params){
		$this->pasa_destino($vec_b, $ind_no, $guia);
		$ids_noestar = '';
		if($ind_f != null)
			$this->contador[$ind_f]++;
		
		foreach($vec_b as $itm){
			if($ind_f==null)
				$this->contador[$itm] = 1;
			
			if(array_search($itm, $this->resultados) === false && $this->contador[($ind_f==null? $itm: $ind_f)] < 3){
				$ids_noestar = implode(',', $this->no_estar[$itm]);
				$res_routs = $this->db->query("
				SELECT regions_id, routes_id2 AS routes_id, distance, lat1, lon1, lat2, lon2  
				FROM routes_distances 
				WHERE regions_id =(SELECT regions_id FROM `routes` WHERE id = ".$itm.")
					AND distance <".$params['filter_radio']."
					AND routes_id1=".$itm."
					AND FIND_IN_SET(routes_id2, '".$ids_noestar."')=0
				UNION
				SELECT regions_id, routes_id1 AS routes_id, distance, lat1, lon1, lat2, lon2  
				FROM routes_distances 
				WHERE regions_id =(SELECT regions_id FROM `routes` WHERE id = ".$itm.")
					AND distance <".$params['filter_radio']."
					AND routes_id2=".$itm."
					AND FIND_IN_SET(routes_id1, '".$ids_noestar."')=0");
				if($res_routs->num_rows() > 0){
					$subdestino = array();
					foreach($res_routs->result_array() as $row){
						$subdestino[] = $row['routes_id'];
					}
					$this->buscar($subdestino, $itm, $guia.$itm.'-', ($ind_f==null? $itm: $ind_f), $params);
				}
				$res_routs->free_result();
			}
		}
	}
	
	/**
	 * Esta funcion determina si una ruta pasa por el destino, lo agrega al array de resultados.
	 * Crea los vectores para no seleccionar rutas ya buscadas.
	 * @param unknown_type $vec_b. array con las rutas a buscar
	 * @param unknown_type $ind_no. ruta enterior.
	 * @param unknown_type $guia. guia de resultados.
	 */
	private function pasa_destino($vec_b, $ind_no, $guia){
		foreach($vec_b as $itm){
			//busca si la ruta pasa por el destino
			if(is_bool(array_search($itm, $this->routes_destino)) === false){
				$this->resultados[] = $itm;
				$this->resultados_guia[$itm] = explode('-', $guia.$itm);
			}
			
			//construye los arrays para no seleccionar rutas ya buscadas.
			if($ind_no != null){
				$this->no_estar[$itm] = isset($this->no_estar[$ind_no])? $this->no_estar[$ind_no]: array();
				$this->no_estar[$itm][] = $ind_no;
			}
			foreach($vec_b as $itm2){
				if($itm != $itm2){
					$this->no_estar[$itm][] = $itm2;
				}
			}
		}
	}
	
	
	
	public function getRoutes2Coor2($params){
		$point = new gPoint();
		$point->setLongLat($params['filter_lon1'], $params['filter_lat1']);
		$point->convertLLtoTM();
		$punto1 = $point->getUTM();
		
		$sql = array('', '');
		if(isset($params['filter_lat2']) && isset($params['filter_lon2'])){
			if(is_numeric($params['filter_lat2']) && is_numeric($params['filter_lon2'])){
				$point->setLongLat($params['filter_lon2'], $params['filter_lat2']);
				$point->convertLLtoTM();
				$punto2 = $point->getUTM();
				$sql[0] = "SELECT rf.id, rf.name, rf.distance FROM (";
				$sql[1] = ") AS rf
					INNER JOIN routes_coordenates AS rco2 ON rf.id = rco2.routes_id
				WHERE inside_circle(rco2.north, rco2.west, ".$punto2['northing'].", ".$punto2['easting'].", ".$params['filter_radio'].") = 1
				GROUP BY rf.id";
			}
		}
		
		$query = Sys::pagination($sql[0]."
		SELECT r.id, r.name, distance2points(rco.north, rco.west, ".$punto1['northing'].", ".$punto1['easting'].") AS distance
		FROM routes AS r 
			INNER JOIN routes_cities AS rc ON r.id = rc.routes_id 
			INNER JOIN (
				SELECT 
					id, 
					city, 
					latitude, 
					longitude, 
					distance2points(north, west, ".$punto1['northing'].", ".$punto1['easting'].") AS distance
				FROM cities_with_routes
				ORDER BY distance ASC LIMIT 1
			) AS ci ON ci.id = rc.cities_id
			INNER JOIN routes_coordenates AS rco ON r.id = rco.routes_id
		WHERE inside_circle(rco.north, rco.west, ".$punto1['northing'].", ".$punto1['easting'].", ".$params['filter_radio'].") = 1
		GROUP BY r.id
		ORDER BY distance ASC".$sql[1], $params, true);
			
		$res_stat = $this->db->query($query['query']);
		if($res_stat->num_rows() > 0){
			$data_rout = array('routes' => array(), 'total_rows' => $query['total_rows']);
			
			foreach($res_stat->result_array() as $row){
				$data_rout['routes'][] = $row;
			}
			
			return $data_rout;
		}
		return false;
	}
	
	
	/**
	 * Script que calcula las distancias entre las rutas de una region y guarda en el servidor
	 * (application/distance) archivos zip con los inserts sql con las distancias de las rutas.
	 * antes de importar estos archivos a la bd se tiene que hacer un DELETE FROM routes_distances WHERE regions_id = "id de la region", 
	 * despues de eso se tiene que importar los archivos generados para esa region.
	 * Recomiendo que este proceso se haga de forma local porque es mucha carga para los servidores mysql y apache, ademas de que
	 * los servidores online no soportan set_time_limit.
	 * @param unknown_type $params
	 */
	public function calDistances($params){
		set_time_limit(0);
		
		$this->load->library('zip');
		
		$params['result_items_per_page'] = 10;
		$params['result_page'] = 1;
		
		$params['num_routes'] = isset($params['num_routes'])? $params['num_routes']: 50;
		$query = Sys::pagination("
		SELECT * 
		FROM  routes 
		WHERE regions_id =".$params['region']."
		ORDER BY id ASC", $params, true);
		$conta = 1;
		$str = '';
		foreach($query['resultset']->result() as $row){
			foreach($query['resultset']->result() as $row2){
				if($row->id != $row2->id && $row2->id > $row->id){
					$res_distan = $this->db->query("SELECT get_closer_distance( ".$row->id.", ".$row2->id." ) AS d;");
					$data_dis = $res_distan->row();
					$info = explode(',', $data_dis->d);
					
					$str .= "INSERT INTO routes_distances (regions_id, routes_id1, routes_id2, distance, lat1, lon1, lat2, lon2) VALUES (".$params['region'].", ".$row->id.", ".$row2->id.", ".$info[0].", '".$info[1]."', '".$info[2]."', '".$info[3]."', '".$info[4]."'); \n";
					$res_distan->free_result();
				}
			}
			
			if($conta == $params['num_routes']){
				$this->zip->add_data($params['region'].'-'.$row->id.".sql", $str);
				$this->zip->archive(APPPATH."distance/".$params['region'].'-'.$row->id.".sql.zip");
				$this->zip->clear_data();
				$str = '';
				$conta = 1;
				$params['num_routes'] += intval($params['num_routes']/3);
			}else $conta++;
		}
		
		if($str != ''){
			$this->zip->add_data($params['region'].'-'."fin.sql", $str);
			$this->zip->archive(APPPATH."distance/".$params['region'].'-'."fin.sql.zip");
			$this->zip->clear_data();
			$str = '';
			$conta = 1;
		}
	}
}
?>
